﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VQData;
using System.Data;
using System.Reflection;
using VQWEB.common;
namespace VQWEB.administrator.sources
{
    public class MembershipData
    {

        public MembershipData() { }

        #region Roles
        public DataTable ViewRoles()
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var query = from r in context.core_user_roles
                            select new
                            {
                                role_id = r.role_id,
                                role_name = r.role_name,
                                role_user = (from u in context.core_users
                                             where r.role_id == u.role_id
                                             select u).Count(),
                                role_module = (from m in context.core_user_role_modules
                                               where m.role_id == r.role_id
                                               select new
                                               {
                                                   module = m.module_name
                                               })
                            };
                if (query.Any())
                    return HelperData.LINQToDataTable(query);
                else
                    return new DataTable();
                
            }
        }
        public bool CheckModuleInRole(int role_id, string module)
        {
            bool flag = false;
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var query = from m in context.core_user_role_modules
                            where (m.role_id == role_id) && (m.module_name == module)
                            select m;
                if (query.Any())
                    flag = true;
            }
            return flag;
        }   
        public int InsertOrUpdateRoles(core_user_role role) {
            int Id = 0;
            try { 
                using(CoreModelDataContext context = new CoreModelDataContext()){
                    var flag = GetRolesById(role.role_id);
                    if (flag != null)
                    {
                        context.core_user_roles.Attach(role);
                        context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, role);
                        context.SubmitChanges();
                        Id = role.role_id;
                       
                    }
                    else {
                        context.core_user_roles.InsertOnSubmit(role);
                        context.SubmitChanges();
                        Id = role.role_id;
                    }                    
                
                }
            
            }
            catch (Exception ex) {
                throw new Exception(ex.Message);
            }
            return Id;
        
        }
        public void AddEditModules(int? role_id, string[] modules)
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                if (role_id.Value > 0)
                {
                    //delete all Module By role_id
                    DeleteModule(new List<int> { role_id.Value });
                    //Insert Module By role_id
                    foreach (string item in modules)
                    {
                        var Obj = new core_user_role_module();
                        Obj.role_id = role_id;
                        Obj.module_name = item;
                        context.core_user_role_modules.InsertOnSubmit(Obj);
                        context.SubmitChanges();
                    }                   
                    

                }
            }

        }
        public void DeleteRoles(List<int> array_role_id)
        {

            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                //Deltete Permission & Role
                DeleteModule(array_role_id);

                var query = from r in context.core_user_roles
                            where array_role_id.Contains(r.role_id)
                            select r;
                if (query.Any())
                {
                    //Update table core_users set role_id = null
                    var obj = from u in context.core_users
                              where array_role_id.Contains(u.role_id.Value)
                              select u;
                    if (obj.Any()) {
                        foreach (var item in obj)
                        {
                            item.role_id = null;
                        }
                        context.SubmitChanges();
                    }
                    //Delete Roles
                    context.core_user_roles.DeleteAllOnSubmit(query);
                    context.SubmitChanges();
                }               
            }
        }
        private void DeleteModule(List<int> array_role_id)
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                //delete all permission
                var query = from p in context.core_user_role_modules
                            where array_role_id.Contains(p.role_id.Value)
                            select p;
                if (query.Any())
                {
                    context.core_user_role_modules.DeleteAllOnSubmit(query);
                    context.SubmitChanges();
                }
            }

        }
        public core_user_role GetRolesById(int? role_id) {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var Obj = context.core_user_roles.Where(r => r.role_id == role_id.Value).SingleOrDefault();
                if (Obj != null)
                    return Obj;
                else
                    return null;
            }
        }
        #endregion

        #region core_users    

        public DataTable ViewUsers()
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {

                var query = from u in context.core_users
                            select new
                            {
                                user_id = u.user_id,
                                user_name = u.user_name,
                                user_active = u.user_active,
                                user_email = u.user_email,
                                user_password = u.user_password,                                
                                role_name = u.core_user_role.role_name,
                                role_module = (from m in context.core_user_role_modules
                                              where m.role_id == u.role_id
                                              select new { 
                                                module = m.module_name
                                              })
                            };
                if (query.Any())
                    return HelperData.LINQToDataTable(query);
                else
                    return new DataTable();
            }

        }
        public core_user GetUsersById(int user_id)
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var query = context.core_users.Where(u => u.user_id.Equals(user_id)).SingleOrDefault();
                if (query != null)
                    return query;
                else
                    return null;
            }

        }
        public core_user GetUsersByName(string user_name)
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var query = context.core_users.Where(u => u.user_name == user_name).SingleOrDefault();
                if (query != null)
                    return query;
                else
                    return null;
            }
        }        
        public string InsertOrUpdateUsers(core_user core_user) {
            string sResponse = string.Empty;
            try
            {
                using (CoreModelDataContext context = new CoreModelDataContext())
                {
                    var flag = GetUsersById(core_user.user_id);
                    if (flag != null)
                    {
                        context.core_users.Attach(core_user);
                        context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, core_user);
                        context.SubmitChanges();
                        sResponse = "Update";
                    }
                    else
                    {
                        context.core_users.InsertOnSubmit(core_user);
                        context.SubmitChanges();
                        sResponse = "Insert";
                    }
                    
                }
            }
            catch (Exception ex)
            {

                sResponse = ex.Message;
            }
            return sResponse;
        }
        public void DeleteUsers(List<int> array_id)
        {
            using (CoreModelDataContext context = new CoreModelDataContext())
            {
                var query = from u in context.core_users
                            where array_id.Contains(u.user_id)
                            select u;
                if (query.Any())
                {
                    context.core_users.DeleteAllOnSubmit(query);
                    context.SubmitChanges();
                }

            }
        }
        public void LockUnlockUsers(List<int> array_id) { 
        
            using(CoreModelDataContext context = new CoreModelDataContext()){
                var query = from u in context.core_users
                            where array_id.Contains(u.user_id)
                            select u;
                if (query.Any())
                {
                    foreach (var item in query)
                    {
                        item.user_active = (item.user_active == true) ? false : true;
                        context.SubmitChanges();
                    }
                }
            
            }
        }       
        #endregion

    }
}